<?php
namespace app\admin\controller;
use app\admin\controller\Base;
use TCPDF;
class Loan extends Base
{
    function export(){
        $prefix = Config('database.prefix');
        $date = Input('date');
        $stime =0;
        $etime =0;
        $stime = strtotime($date.'-01');
        $temp_date = new \DateTime(date('Y-m-d',$stime));
        $etime = $temp_date->add(new \DateInterval('P1M'))->getTimestamp();

		$sql = 'SELECT a.realname,a.money,a.extra,a.loan_time,a.final_money,a.step,p.money AS pmoney, FROM_UNIXTIME(p.date,"%Y-%m-%d") AS pdate FROM '.$prefix.'loan AS a LEFT JOIN '.$prefix.'profit AS p ON p.pid = a.id WHERE (p.date BETWEEN '.$stime.' AND '.$etime.') order by a.step asc ,a.date asc';
		//$sql = 'SELECT * from '.$prefix.'loan where step="c" or (end_time BETWEEN "'.date('Y-m-d',$stime).'" and "'.date('Y-m-d',$etime).'") ORDER by date asc';
		$data =	\Db::query($sql);
		//echo $sql;
		$sql1 = 'SELECT a.realname,a.money,a.extra,a.loan_time,a.final_money,a.step,concat("") AS pmoney,concat("") AS pdate FROM '.$prefix.'loan AS a WHERE a.step="x" and (UNIX_TIMESTAMP(a.end_time) BETWEEN '.$stime.' AND '.$etime.') order by a.step asc,a.date asc';
		//echo $sql1;
		$data1 = \Db::query($sql1);
		//P($data1);
		//exit;
		$data = array_merge($data,$data1);
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->mergeCells('B1:J1');
        $sheet->setCellValue('B1', '抵押贷款');
        $sheet->setCellValue('K1', $date);
        $sheet->setCellValue('A2', '序号');
        $sheet->setCellValue('B2', '客户姓名');
        $sheet->setCellValue('C2', '品牌');
        $sheet->setCellValue('D2', '借款本金');
        $sheet->setCellValue('E2', '计息方式');
        $sheet->setCellValue('F2', '付款日期');
        $sheet->setCellValue('G2', '实际付款');
        $sheet->setCellValue('H2', '结清日期');
        $sheet->setCellValue('I2', '当前状态');
        $sheet->setCellValue('J2', '明细');
        $sheet->setCellValue('K2', '利润');
        $i = 3;
        foreach ($data as $key => $vo) {
			$vo['extra'] = json_decode($vo['extra'],true);
			/*P($vo);
			exit;
            $sql = 'SELECT * from '.$prefix.'profit where pid = '.$vo['id'].' and (date BETWEEN '.$stime.' AND '.$etime .')';
            //echo $sql;
            //exit;
            $pvo = \Db::query($sql);
			$pvo=$pvo[0];*/
            $sheet->setCellValue('A'.($i+$key), '=ROW()-2');
            $sheet->setCellValue('B'.($i+$key), $vo['realname']);
            $sheet->setCellValue('C'.($i+$key), $vo['extra']['car_info']);
            $sheet->setCellValue('D'.($i+$key), $vo['money']);
            $sheet->setCellValue('E'.($i+$key), $vo['extra']['jxfs']);
            $sheet->setCellValue('F'.($i+$key), $vo['loan_time']);
            $sheet->setCellValue('G'.($i+$key), $vo['final_money']);    
            $sheet->setCellValue('H'.($i+$key), str_replace('0000-00-00','',$vo['end_time']));       
            $sheet->setCellValue('I'.($i+$key), Linkage($vo['step'],'BorrowStep'));     
            $sheet->setCellValue('J'.($i+$key), $vo['pdate'] ? $vo['pdate'].'    '.$vo['pmoney'] : '');   
            $sheet->setCellValue('K'.($i+$key), $vo['pmoney']);
            //P($value);
        }
        $row = count($data)+2;
        $sheet->setCellValue('K'.($row+1),'=SUM(K3:K'.$row.')');
        $sheet->setCellValue('D'.($row+1),'=SUM(D3:D'.$row.')');
        $sheet->setCellValue('A'.($row+1),'统计');

        $sheet->getColumnDimension('A')->setWidth(6);
        $sheet->getColumnDimension('B')->setWidth(9);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(9);
        $sheet->getColumnDimension('E')->setWidth(9);
        $sheet->getColumnDimension('F')->setWidth(11);
        $sheet->getColumnDimension('G')->setWidth(9);
        $sheet->getColumnDimension('H')->setWidth(11);
        $sheet->getColumnDimension('I')->setWidth(9);
        $sheet->getColumnDimension('J')->setWidth(16);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getDefaultRowDimension()->setRowHeight(20);
        $sheet->getRowDimension('1')->setRowHeight(40);

        $sheet->getStyle('A1:K'.$row)->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'alignment' => [
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ]);

        $sheet->getStyle('B1')->applyFromArray([
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ]);

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $path = '/Uploads/monthtable/loan_'.$date.'.xlsx';
        $writer->save('.'.$path);
        $this->success('success',$path);
        exit('export');
    }
    function export_all_data(){
        $prefix = Config('database.prefix');
        $data = \Db::query('SELECT * from '.$prefix.'loan where step<>"x" ORDER by step asc,date asc');

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->setCellValue('A1', '序号');
        $sheet->setCellValue('B1', '客户姓名');
        $sheet->setCellValue('C1', '抵押物');
        $sheet->setCellValue('D1', '借款金额');
        $sheet->setCellValue('E1', '计息方式');
        $sheet->setCellValue('F1', '付款日期');
        $sheet->setCellValue('G1', '实际付款');
        $sheet->setCellValue('H1', '结清日期');
        $sheet->setCellValue('I1', '当前状态');
        $sheet->setCellValue('J1', '录入日期');
        $sheet->setCellValue('K1', '借款期限');
        $sheet->setCellValue('L1', '提醒时间');
        $i = 2;
        foreach ($data as $key => $vo) {
            $vo['extra'] = json_decode($vo['extra'],true);
            $sheet->setCellValue('A'.($i+$key), $key+1);
            $sheet->setCellValue('B'.($i+$key), $vo['realname']);
            $sheet->setCellValue('C'.($i+$key), $vo['extra']['car_info']);
            $sheet->setCellValue('D'.($i+$key), $vo['money']);
            $sheet->setCellValue('E'.($i+$key), $vo['extra']['jxfs']);
            $sheet->setCellValue('F'.($i+$key), $vo['loan_time']);
            $sheet->setCellValue('G'.($i+$key), $vo['final_money']); 
            $sheet->setCellValue('H'.($i+$key), str_replace('0000-00-00','',$vo['end_time']));       
            $sheet->setCellValue('I'.($i+$key), Linkage($vo['step'],'BorrowStep'));     
            $sheet->setCellValue('J'.($i+$key), $vo['date']);
            $sheet->setCellValue('K'.($i+$key), $vo['peroid']);
            $sheet->setCellValue('L'.($i+$key), $vo['remind_time']);
            //P($value);
        }
        $row = count($data)+1;
        $sheet->getDefaultRowDimension()->setRowHeight(20);
        $sheet->getStyle('A1:L'.$row)->applyFromArray([
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
            'alignment' => [
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ]);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $path = '/Uploads/monthtable/export_all_data.xlsx';
        $writer->save('.'.$path);
        header('Location:'.$path);
        //$this->success('success',$path);
        exit('export');
    }
    public function repay(){
        return view();
        /*$Income = model('Income');
        $id = input('id');
        $list = $Income->where('res_id',$id)->Order('id asc')->select();
        return view('',['list'=>$list]);*/
    }
    public function add(){
        return view('edit',[
            'vo' => [
                'step'   =>  'a',
                'final_money'   =>  0,
                'month'         =>  3,
            ]
        ]);
    }
    protected function validData($data=[]){
        $rule = [
            'idcard|申请人身份证号'    =>'alphaNum',
            'phone|申请人联系手机'     =>'number',
            'jxfs|计息方式'      =>'require',
            'sos_name1|联系人一姓名' =>'chs',
            'sos_sex1|联系人一性别'  =>'number',
            'sos_rela1|联系人一关系' =>'alpha',
            'sos_phone1|联系人一手机'=>'number',
            'sos_name2|联系人二姓名' =>'chs',
            'sos_sex2|联系人二性别'  =>'number',
            'sos_rela2|联系人二关系' =>'alpha',
            'sos_phone2|联系人二手机'=>'number',
            'car_info|车辆信息'  =>'require'
        ];
        $data = $data['extra'];
        $validate   = \think\Validate::make($rule);
        $result = $validate->check($data);

        if(!$result) {
            $this->error($validate->getError());
        }
    }
    protected function _filterData($data=[]){
        $data['gps'] = explode("\\n", $data['gps']);
        $this->validData($data);
        return $data;
    }
    function tools(){
        return view();
    }
    function toword(){
        $id= Input('id');
        $ret =\ORG\Rec2File::create($id,'word');
        header('Location:'.$ret['word']);
        exit;
    }
    function getList(){
		$model = model('Loan');
        $map = [
            'keywords'=> input('keywords'),
            'pagesize'=>input('limit'),
            'date'=>input('date'),
            'step'=>input('step'),
			'order'=>'date desc'
        ];
        if(input('field') && input('order')){
            $map['order'] = input('field').' '.input('order');
        }
        $list = $model->getList($map);

        $a = $list['list']->toArray();
        $data = $a['data'];
        foreach ($data as $k => $v) {
            $data[$k]['phone'] = $v['extra']['phone'];
            //$data[$k]['gps'] = $v['extra']['gps'];
        }
        $a['data'] = $data;
        $a['code'] = 0;
        $a['msg']  = '';
        $a['count']  = $a['total'];
        echo json_encode($a);
    }

    public function attr(){
        $field = input('field');
        $id = input('ids');
        $val = input('val');
		$model = model('Loan');
        $data = [$field=>$val];
        $fs=[$field];
        if($field == "step" && $val=="g"){
            $data['end_time'] = date('Y-m-d');
            $fs[] = 'end_time';
        }
        $model->allowField($fs)->where('id', $id)->update($data);
        //exit($model->getlastsql());
        $this->success('操作成功！');
        //P($val);
        //exit;
    }
	public function export_loan_doc(){
        $id= Input('id');
		$ret = [];
        $vo = model('Loan')->getView(['id'=>$id]);        
        $ret['realname'] = $vo['realname'];
        $ret['idcard'] = $vo['extra']['idcard'];
        $BasePath = "/uploads/loan/".$vo['id'].'/';
        $RootPath = \Env::get('root_path').'public_html';
		$PHPWord = new \PhpOffice\PhpWord\PhpWord();
		$ret['pdf'] = $BasePath.$ret['realname'].'_'.$ret['idcard'].'.pdf';
		$ret['word'] = $BasePath.$ret['realname'].'_'.$ret['idcard'].'.docx';
		if(!file_exists($ret['word']) || $refresh==true){
			$fn = $vo['realname'].' '.$vo['extra']['idcard'].'.docx';
			$vo['money'] = $vo['money']*10000;
			$arr = [
				'loan_time'		=> date('Y年m月d日',strtotime($vo['loan_time'])),
				'repay_time'	=> date('Y年m月d日',strtotime('+'.$vo['peroid'].' month',strtotime($vo['loan_time']))),
				'money'			=> number_format($vo['money'],2),
				'dx_money'		=> $this->RMB($vo['money'])
			];
			$document = $PHPWord->loadTemplate(\Env::get('root_path').'/public_html/static/tpl/loan_doc.docx');
			foreach ($arr as $key => $value) {
				$document->setValue('{='.$key.'}' , $value);
			}
			@mkdir($RootPath.$BasePath);
			/*try{
				mkdir($RootPath.$BasePath);
			}catch(Exception $e){
				throw new Exception($e);
			}*/
			$word_fn = $RootPath.iconv('UTF-8','GB2312//IGNORE', $ret['word']);
			$document->saveAs($word_fn,true);
			$Reader = \PhpOffice\PhpWord\IOFactory::createReader('Word2007');
			$phpword = $Reader->load($word_fn);
			\PhpOffice\PhpWord\Settings::setPdfRenderer(\PhpOffice\PhpWord\Settings::PDF_RENDERER_TCPDF,\Env::get('root_path') . 'vendor/'.'tecnickcom/tcpdf/');
			$phpword->save($RootPath.iconv('UTF-8','GB2312//IGNORE', $ret['pdf']),'PDF');
		}

        header('Location:'.$ret['word']);
        exit;	
	}
	/**
	 * 数字金额转换成中文大写金额,要求小数位数为两位
	 * @param $num 要转换的小写数字或小写字符串
	 * @return 大写金额字符串
	 */
	function RMB($num){
		$c1 = "零壹贰叁肆伍陆柒捌玖";
		$c2 = "分角元拾佰仟万拾佰仟亿";
		$num = round($num, 2);
		$num = $num * 100;
		if (strlen($num) > 11) {
			return "数据太长，没有这么大的钱吧，检查下";
		}
		$i = 0;
		$c = "";
		while (1) {
			if ($i == 0) {
				$n = substr($num, strlen($num)-1, 1);
			} else {
				$n = $num % 10;
			}
			$p1 = substr($c1, 3 * $n, 3);
			$p2 = substr($c2, 3 * $i, 3);
			if ($n != '0' || ($n == '0' && ($p2 == '亿' || $p2 == '万' || $p2 == '元'))) {
				$c = $p1 . $p2 . $c;
			} else {
				$c = $p1 . $c;
			}
			$i = $i + 1;
			$num = $num / 10;
			$num = (int)$num;
			if ($num == 0) {
				break;
			}
		}
		$j = 0;
		$slen = strlen($c);
		while ($j < $slen) {
			$m = substr($c, $j, 6);
			if ($m == '零元' || $m == '零万' || $m == '零亿' || $m == '零零') {
				$left = substr($c, 0, $j);
				$right = substr($c, $j + 3);
				$c = $left . $right;
				$j = $j-3;
				$slen = $slen-3;
			}
			$j = $j + 3;
		}
	 
		if (substr($c, strlen($c)-3, 3) == '零') {
			$c = substr($c, 0, strlen($c)-3);
		}
		if (empty($c)) {
			return "零元整";
		}else{
			return $c . "整";
		}
	}
}
